# -*- coding: utf-8 -*-
# C:\Users\ADMIN\Desktop\testXLWings.xlsx
import os
import sys

import win32com
import xlwings as xw
from xlwings import Range as RNG, Book
import time
import numpy as np

from Utils import TxtUtil, DirUtil, IOUtil, TimeUtil, ConvertUtil

logFileName = os.path.basename(__file__).split(".")[0]
from Statistics.COMMON import commonLogger

log = commonLogger
from Statistics.COL_NUMS import CHAR_0_25 as c
import win32com.client


def getLastRowNum(wb: Book, sheetName=0):
    return wb.sheets[sheetName].range('A' + str(wb.sheets[sheetName].cells.last_cell.row)).end('up').row


def getNewRowNum(wb: Book, sheetName=0):
    return 1 + getLastRowNum(wb, sheetName)





def getTitleModule(app, path: str):
    wb = app.books.open(path)
    return wb


# def readAsListByRow(wb:Book, sheetName=0):
#     rowNum = wb.sheets[sheetName].used_range.last_cell.row
#     log.info("yzlog rowNum:", rowNum)
#     rngData = []
#     for i in range(rowNum):
#         rowData = wb.sheets[sheetName].range('A'+str(i+1)).expand('right').value
#         log.info("yzlog rowData:", rowData)
def readTitle(wb: Book, sheetName=0):
    data = wb.sheets[sheetName].range('A1').expand('right').value
    print("data4ReadTitle:", data)
    return data


def getTitleAsNumDict(wb: Book, sheetName=0, cache=None):
    if cache is not None:
        return cache
    data = wb.sheets[sheetName].range('A1').expand('right').value
    # print("data4getTitleAsNumDict:", data)
    numDict = TxtUtil.getColNumByName(arr=data)
    # log.info("yzlog numDict:", numDict)
    return numDict
def readAsListByCol(wb: Book, sheetName=0):
    colNum = wb.sheets[sheetName].used_range.last_cell.column
    # log.info("yzlog colNum:", colNum)
    rowNum = wb.sheets[sheetName].used_range.last_cell.row - 1
    # log.info("yzlog rowNum:", rowNum)
    rngData = []
    titleArr = []
    for i in range(colNum):
        colData = wb.sheets[sheetName].range(c[i] + '2').expand('down').value
        # log.info("yzlog colData:", colData)
        if colData is not None:
            if len(colData) == rowNum:
                rngData.append(colData)
            else:
                rngData.append([None] * rowNum)
                for j in range(rowNum):
                    rngData[i][j] = wb.sheets[sheetName].range(j + 2, i + 1).value
                    # log.info("yzlog i+1,j+2:", i+1,j+2)
                    # log.info("yzlog wb.sheets[sheetName].range(j+2,i+1).value:", wb.sheets[sheetName].range(j+2,i).value)
                    # log.info("yzlog rngData[i][j]:", rngData[i][j])
        else:
            rngData.append([None] * rowNum)
        # log.info("yzlog colData:", colData)
        field = wb.sheets[sheetName].range(c[i] + '1').value
        titleArr.append(field)
    # log.info("yzlog rngData bf transpose:", rngData)
    rngData = np.transpose(rngData)
    # log.info("yzlog title:", titleArr)
    # log.info("yzlog rngData af transpose:", rngData)
    return {"titleArr": titleArr, "rngData": rngData}
def readbyCellIJ(wb: Book, sheetName=0):
    colNum = wb.sheets[sheetName].used_range.last_cell.column
    # log.info("yzlog colNum:", colNum)
    rowNum = wb.sheets[sheetName].used_range.last_cell.row - 1
    tbArr = []
    for i in range(rowNum):
        tbArr.append([])
        for j in range(colNum):
            tbArr[i].append(wb.sheets[sheetName].range(i + 2, j + 1).value)
        if len(tbArr[i]) != colNum:
            log.error("yzlog !=colNum tbArr[",i,"]:", tbArr[i])
    if len(tbArr) != rowNum:
        log.error("yzlog !=rowNum tbArr", tbArr[i])
    return tbArr
def append1Row(wb: Book, content: list, sheetName=0):
    newRowNum = getNewRowNum(wb, sheetName)
    wb.sheets[sheetName].range('a' + str(newRowNum)).value = content

def updateXL(wb: Book, arr: list, sheetName=0):
    updateXLByCol(wb, np.transpose(arr), sheetName)


def updateXLByCol(wb: Book, arr: list, sheetName=0):
    # log.info("yzlog arr af transpose:", arr)
    if len(arr) <= 0:
        return
    rowNum = len(arr[0])
    for i in range(len(arr)):
        if True or arr[i][0] != None:
            # wb.sheets[sheetName].range(2, i + 1).options(transpose=True).value = arr[i]
            for j in range(rowNum):
                if arr[i][j] != None:
                    wb.sheets[sheetName].range(2 + j, i + 1).value = arr[i][j]


def updateXLbyCellIJ(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for i in range(len(arr)):
        for j in range(len(arr[i])):
            wb.sheets[sheetName].range(i + 2, j + 1).value = arr[i][j]
def updateXLbyCellIJ_XQ_Articles(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for i in range(len(arr)):
        for j in range(len(arr[i])):
            wb.sheets[sheetName].range(i + 2, j + 1).value = arr[i][j]
def updateXLbyCellIJ_XQ_Users(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for i in range(len(arr)):
        for j in range(len(arr[i])):
            wb.sheets[sheetName].range(i + 2, j + 1).value = arr[i][j]
def updateXLbyCellIJ_XHS_Links(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for i in range(len(arr)):
        for j in range(len(arr[i])):
            if len(arr[i][j])<10 and str(arr[i][j]).endswith('万'):
                numExpand = ConvertUtil.numStr2numInt(arr[i][j])
                try:
                    int(numExpand)
                except:
                    numExpand = "--"
                wb.sheets[sheetName].range(i + 2, j + 1).value = numExpand
            else:
                wb.sheets[sheetName].range(i + 2, j + 1).value = arr[i][j]
def updateXLbyCellIJ_XHS_Comments(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for i in range(len(arr)):
        for j in range(len(arr[i])):
            if len(arr[i][j])<10 and str(arr[i][j]).endswith('万'):
                wb.sheets[sheetName].range(i + 2, j + 1).value = ConvertUtil.numStr2numInt(arr[i][j])
            else:
                wb.sheets[sheetName].range(i + 2, j + 1).value = arr[i][j]

def updateXLbyCellJI(wb: Book, arr: list, sheetName=0):
    if len(arr) <= 0:
        return
    for j in range(len(arr)):
        for i in range(len(arr[j])):
            wb.sheets[sheetName].range(i + 2, j + 1).value = arr[j][i]


def statisticsXL(wb: Book, sheetName=0):
    colNum = wb.sheets[sheetName].used_range.last_cell.column
    # log.info("yzlog colNum:", colNum)
    rowNum = wb.sheets[sheetName].used_range.last_cell.row
    # log.info("yzlog rowNum:", rowNum)
    wb.sheets[sheetName].range(rowNum + 2, colNum + 2).value = "=SUM(A%s:%s%s)" % (rowNum + 2, c[colNum - 1], rowNum + 2)
    for j in range(colNum):
        wb.sheets[sheetName].range(rowNum + 2, j + 1).value = "=SUM(%s2:%s%s)" % (c[j], c[j], rowNum)
        wb.sheets[sheetName].range(rowNum + 3, j + 1).value = "=ROUND(AVERAGE(%s2:%s%s), 2)" % (c[j], c[j], rowNum)
        wb.sheets[sheetName].range(rowNum + 4, j + 1).value = "=ROUND(%s%s/$%s$%s,3)" % (c[j],rowNum+2,c[colNum+2-1],rowNum+2)
    # def readAsDict(wb:Book, sheetName=0):


#     data = wb.sheets[sheetName].range('B2').expand('down').value
#     print(data)
#     return data

def changeTile(wb: Book, reflectDict:dict, sheetName=0):
    bfTitleArr = readTitle(wb)
    for j in range(len(bfTitleArr)):
        if bfTitleArr[j] in reflectDict:
            wb.sheets[sheetName].range(1, j + 1).value = reflectDict[bfTitleArr[j]]




def isExcelOpen(filepath):
    # 判断Excel文件是否已打开
    # 如果目标工作簿已打开则返回TRUE，否则返回FALSE
    excelapp = win32com.client.Dispatch("Excel.Application")
    flag = False

    num = excelapp.Workbooks.Count
    if num > 0:
        for i in range(1, num + 1):
            realpth = excelapp.Workbooks(i).Path + "\\" + excelapp.Workbooks(i).Name
            if filepath == realpth:
                flag = True
                break
    return flag


if __name__ == '__main__':
    # sys.exit(0)
    # o = {'reqTime': 0, 'symbol': 1, 'money': 2, 'ts': 3, 'direction': 4, 'cycle': 5, 'startPrice': 6, 'endPrice': 7, 'dynamicRatio': 8, 'pureProfit': 9, 'endTime': 10, 'type': 11, 'orderId': 12, 'money1': 13, 'money2': 14, 'money3': 15, 'money4': 16, 'resTime1': 17, 'resTime2': 18}
    # log.info("yzlog len(o):", len(o))
    # content=[None]*len(o)
    # content[o['startPrice']]=55.0
    # log.info("yzlog content:", content)
    app = xw.App(visible=False, add_book=False)

    t0 = time.time()
    filePath = os.path.join(DirUtil.getDesktopDir(),r"Module_XQ_Users.xlsx")
    if isExcelOpen(filePath):
        print("Has open:", filePath)
        sys.exit(0)
    wb = app.books.open(filePath)
    t1 = time.time()
    log.info("yzlog t1-t0:", round(t1 - t0, 3))
    srcFileNa = "xq_users_249cc885"
    srcFileTxt = srcFileNa+".txt"
    txtFilePath = os.path.join(DirUtil.getDesktopDir(), srcFileTxt)
    t101 = time.time()
    arr = IOUtil.file2ArrArr(txtFilePath)
    t102 = time.time()
    print(f'yzprt t102-t101:{round(t102-t101,3)}')
    # 以下是测试操作
    # readbyCellIJ(wb)
    updateXLbyCellIJ_XQ_Articles(wb,arr)
    t103 = time.time()
    print(f'yzprt t103-t102:{round(t103-t102,3)}')
    # changeTile(wb,COL_NUMS.CASE_PAY_TITLE_AS)
    # wb.sheets[0].range(0, 0).value = '苦短'
    # append1Row(wb,[1,2,3,4,5])
    # readAsListByCol(wb)
    # updateXL(wb,arr)
    # arr = readTitle(wb)
    # res = TxtUtil.getColNumByName(arr=arr)
    # print(res)
    # print(type(res))
    # 以上是测试操作
    t2 = time.time()
    log.info("yzlog t2-t1:", round(t2 - t1, 2))
    fileNa,ex = os.path.splitext(srcFileTxt)
    wb.save(os.path.join(DirUtil.getDesktopDir(), srcFileNa+"_"+TimeUtil.stampS2TimeStr(TimeUtil.getStamp10(),fmt="mdHMS")+".xlsx"))
    # wb.save()
    t3 = time.time()
    log.info("yzlog t3-t2:", round(t3 - t2, 2))
    wb.close()
    t4 = time.time()
    log.info("yzlog t4-t3:", round(t4 - t3, 2))
    app.quit()
    t5 = time.time()
    log.info("yzlog t5-t4:", round(t5 - t4, 2))

    log.info("yzlog t99-t0:", round(t5 - t0, 2))
